using Microsoft.EntityFrameworkCore;
using ERPBackend.Models;
using System.Linq;
namespace ERPBackend.Data
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {}

        // 辅助方法：将PascalCase转换为snake_case
        private string ConvertPascalToSnakeCase(string input)
        {
            if (string.IsNullOrEmpty(input))
                return input;

            return string.Concat(input.Select((c, i) => i > 0 && char.IsUpper(c) ? "_" + char.ToLower(c) : char.ToLower(c).ToString())).TrimStart('_');
        }

        public DbSet<FirstLevelCategory> FirstLevelCategories { get; set; }
        public DbSet<SecondLevelCategory> SecondLevelCategories { get; set; }
        public DbSet<Material> Materials { get; set; }
        public DbSet<Supplier> Suppliers { get; set; }
        public DbSet<SupplierContact> SupplierContacts { get; set; }
        public DbSet<SupplierQualification> SupplierQualifications { get; set; }
        public DbSet<User> Users { get; set; }
        public DbSet<Role> Roles { get; set; }
        public DbSet<UserRole> UserRoles { get; set; }
        public DbSet<Department> Departments { get; set; }
        public DbSet<RolePermission> RolePermissions { get; set; }
        public DbSet<PurchaseOrder> PurchaseOrders { get; set; }
        public DbSet<PurchaseOrderItem> PurchaseOrderItems { get; set; }
        public DbSet<PurchaseReceipt> PurchaseReceipts { get; set; }
        public DbSet<PurchaseReceiptItem> PurchaseReceiptItems { get; set; }
        public DbSet<PurchaseStockIn> PurchaseStockIns { get; set; }
        public DbSet<PurchaseStockInItem> PurchaseStockInItems { get; set; }
        public DbSet<Warehouse> Warehouses { get; set; }
        public DbSet<Inventory> Inventories { get; set; }
        public DbSet<InventoryTransaction> InventoryTransactions { get; set; }
        public DbSet<Workshop> Workshops { get; set; }
        public DbSet<ProductionStockIn> ProductionStockIns { get; set; }
        public DbSet<ProductionStockInItem> ProductionStockInItems { get; set; }
        public DbSet<OtherStockIn> OtherStockIns { get; set; }
        public DbSet<OtherStockInItem> OtherStockInItems { get; set; }
        public DbSet<Customer> Customers { get; set; }
        public DbSet<CustomerContact> CustomerContacts { get; set; }
        public DbSet<SalesContract> SalesContracts { get; set; }
        public DbSet<SalesContractItem> SalesContractItems { get; set; }
        public DbSet<SalesOrder> SalesOrders { get; set; }
        public DbSet<SalesOrderItem> SalesOrderItems { get; set; }
        public DbSet<EBOM> EBOMs { get; set; }
        public DbSet<EBOMItem> EBOMItems { get; set; }
        public DbSet<EBOMChange> EBOMChanges { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // 全局配置：表名和列名使用下划线命名
            foreach (var entityType in modelBuilder.Model.GetEntityTypes())
            {
                // 配置表名
                entityType.SetTableName(ConvertPascalToSnakeCase(entityType.ClrType.Name));

                // 配置列名
                foreach (var property in entityType.GetProperties())
                {
                    property.SetColumnName(ConvertPascalToSnakeCase(property.Name));
                }

                // 配置外键列名
                foreach (var foreignKey in entityType.GetForeignKeys())
                {
                    foreach (var property in foreignKey.Properties)
                    {
                        property.SetColumnName(ConvertPascalToSnakeCase(property.Name));
                    }
                }
            }

            // 配置一级分类和二级分类的关系
            modelBuilder.Entity<SecondLevelCategory>()
                .HasOne<FirstLevelCategory>(s => s.FirstLevelCategory)
                .WithMany(f => f.SecondLevelCategories)
                .HasForeignKey(s => s.FirstLevelCategoryId);

            // 配置CategoryCode唯一
            modelBuilder.Entity<FirstLevelCategory>()
                .HasIndex(f => f.CategoryCode)
                .IsUnique();

            // 配置二级分类CategoryCode唯一
            modelBuilder.Entity<SecondLevelCategory>()
                .HasIndex(s => s.CategoryCode)
                .IsUnique();

            // 配置物料与二级分类的关系
            modelBuilder.Entity<Material>()
                .HasOne<SecondLevelCategory>(m => m.SecondLevelCategory)
                .WithMany(s => s.Materials)
                .HasForeignKey(m => m.SecondLevelCategoryId);

            // 配置物料编码唯一
            modelBuilder.Entity<Material>()
                .HasIndex(m => m.MaterialCode)
                .IsUnique();

            // 配置EBOM和EBOMItem的关系
            modelBuilder.Entity<EBOMItem>()
                .HasOne<EBOM>(b => b.EBOM)
                .WithMany(e => e.EBOMItems)
                .HasForeignKey(b => b.BomId);

            // 配置EBOM和EBOMChange的关系
            modelBuilder.Entity<EBOMChange>()
                .HasOne<EBOM>(b => b.EBOM)
                .WithMany(e => e.EBOMChanges)
                .HasForeignKey(b => b.BomId);

            // 配置EBOMItem和Material的关系
            modelBuilder.Entity<EBOMItem>()
                .HasOne<Material>(b => b.Material)
                .WithMany()
                .HasForeignKey(b => b.MaterialId)
                .OnDelete(DeleteBehavior.NoAction);

            // 配置EBOM的BomCode和Version组合唯一
            modelBuilder.Entity<EBOM>()
                .HasIndex(e => new { e.BomCode, e.Version })
                .IsUnique();

            // 配置物料编码唯一
            modelBuilder.Entity<Material>()
                .HasIndex(m => m.MaterialCode)
                .IsUnique();

            // 配置供应商编码唯一
            modelBuilder.Entity<Supplier>()
                .HasIndex(s => s.SupplierCode)
                .IsUnique();

            // 配置客户编码唯一
            modelBuilder.Entity<Customer>()
                .HasIndex(c => c.CustomerCode)
                .IsUnique();

            // 配置客户与客户联系人的关系
            modelBuilder.Entity<CustomerContact>()
                .HasOne<Customer>(cc => cc.Customer)
                .WithMany(c => c.CustomerContacts)
                .HasForeignKey(cc => cc.CustomerId);

            // 配置销售合同编码唯一
            modelBuilder.Entity<SalesContract>()
                .HasIndex(sc => sc.ContractCode)
                .IsUnique();

            // 配置销售合同与销售合同明细的关系
            modelBuilder.Entity<SalesContractItem>()
                .HasOne<SalesContract>(sci => sci.SalesContract)
                .WithMany(sc => sc.SalesContractItems)
                .HasForeignKey(sci => sci.SalesContractId);

            // 配置销售合同明细的唯一性约束(销售合同ID+行号)
            modelBuilder.Entity<SalesContractItem>()
                .HasIndex(sci => new { sci.SalesContractId, sci.LineNumber })
                .IsUnique();

            // 配置销售订单编码唯一
            modelBuilder.Entity<SalesOrder>()
                .HasIndex(so => so.OrderCode)
                .IsUnique();

            // 配置销售订单与销售订单明细的关系
            modelBuilder.Entity<SalesOrderItem>()
                .HasOne<SalesOrder>(soi => soi.SalesOrder)
                .WithMany(so => so.SalesOrderItems)
                .HasForeignKey(soi => soi.SalesOrderId);

            // 配置销售订单明细的唯一性约束(销售订单ID+行号)
            modelBuilder.Entity<SalesOrderItem>()
                .HasIndex(soi => new { soi.SalesOrderId, soi.LineNumber })
                .IsUnique();

            // 配置用户与部门的关系
            modelBuilder.Entity<User>()
                .HasOne<Department>()
                .WithMany(d => d.Users)
                .HasForeignKey(u => u.DepartmentId);

            // 配置用户与角色的多对多关系
            modelBuilder.Entity<UserRole>()
                .HasKey(ur => new { ur.UserId, ur.RoleId });

            modelBuilder.Entity<UserRole>()
                .HasOne<User>()
                .WithMany(u => u.UserRoles)
                .HasForeignKey(ur => ur.UserId)
                .IsRequired();

            modelBuilder.Entity<UserRole>()
                .HasOne<Role>()
                .WithMany(r => r.UserRoles)
                .HasForeignKey(ur => ur.RoleId)
                .IsRequired();

            // 配置部门层级关系，使用子级导航属性
            modelBuilder.Entity<Department>()
                .HasOne<Department>()
                .WithMany(d => d.ChildDepartments)
                .HasForeignKey(d => d.ParentDepartmentId);

            // 配置部门经理关系，User模型中无对应导航属性
            modelBuilder.Entity<Department>()
                .HasOne<User>()
                .WithMany()
                .HasForeignKey(d => d.ManagerId);

            // 配置用户编码唯一
            modelBuilder.Entity<User>()
                .HasIndex(u => u.Username)
                .IsUnique();

            // 配置部门编码唯一
            modelBuilder.Entity<Department>()
                .HasIndex(d => d.DepartmentCode)
                .IsUnique();

            // 配置角色名称唯一
            modelBuilder.Entity<Role>()
                .HasIndex(r => r.RoleName)
                .IsUnique();

            // 配置采购订单和采购订单明细的关系
            modelBuilder.Entity<PurchaseOrderItem>()
                .HasOne<PurchaseOrder>(poi => poi.PurchaseOrder)
                .WithMany(po => po.PurchaseOrderItems)
                .HasForeignKey(poi => poi.PurchaseOrderId);

            // 配置采购订单编码唯一
            modelBuilder.Entity<PurchaseOrder>()
                .HasIndex(po => po.OrderCode)
                .IsUnique();

            // 配置采购订单明细的唯一性约束(订单ID+行号)
            modelBuilder.Entity<PurchaseOrderItem>()
                .HasIndex(poi => new { poi.PurchaseOrderId, poi.LineNumber })
                .IsUnique();

            // 配置采购到货单和采购到货单明细的关系
            modelBuilder.Entity<PurchaseReceiptItem>()
                .HasOne<PurchaseReceipt>(pri => pri.PurchaseReceipt)
                .WithMany(pr => pr.PurchaseReceiptItems)
                .HasForeignKey(pri => pri.PurchaseReceiptId);

            // 配置采购到货单和采购订单的关系
            modelBuilder.Entity<PurchaseReceipt>()
                .HasOne<PurchaseOrder>(pr => pr.PurchaseOrder)
                .WithMany()
                .HasForeignKey(pr => pr.PurchaseOrderId);

            // 配置采购到货单和供应商的关系
            modelBuilder.Entity<PurchaseReceipt>()
                .HasOne<Supplier>(pr => pr.Supplier)
                .WithMany()
                .HasForeignKey(pr => pr.SupplierId)
                .OnDelete(DeleteBehavior.NoAction);

            // 配置采购订单和供应商的关系
            modelBuilder.Entity<PurchaseOrder>()
                .HasOne<Supplier>(po => po.Supplier)
                .WithMany()
                .HasForeignKey(po => po.SupplierId)
                .OnDelete(DeleteBehavior.NoAction);

            // 配置采购到货单和仓库的关系
            modelBuilder.Entity<PurchaseReceipt>()
                .HasOne<Warehouse>(pr => pr.Warehouse)
                .WithMany(w => w.PurchaseReceipts)
                .HasForeignKey(pr => pr.WarehouseId);

            // 配置采购到货单明细和采购订单明细的关系
            modelBuilder.Entity<PurchaseReceiptItem>()
                .HasOne<PurchaseOrderItem>(pri => pri.PurchaseOrderItem)
                .WithMany()
                .HasForeignKey(pri => pri.PurchaseOrderItemId);

            // 配置采购到货单明细和物料的关系
            modelBuilder.Entity<PurchaseReceiptItem>()
                .HasOne<Material>(pri => pri.Material)
                .WithMany()
                .HasForeignKey(pri => pri.MaterialId);

            // 配置采购到货单编码唯一
            modelBuilder.Entity<PurchaseReceipt>()
                .HasIndex(pr => pr.ReceiptCode)
                .IsUnique();

            // 配置仓库编码唯一
            modelBuilder.Entity<Warehouse>()
                .HasIndex(w => w.WarehouseCode)
                .IsUnique();

            // 配置采购到货单明细的唯一性约束(到货单ID+行号)
            modelBuilder.Entity<PurchaseReceiptItem>()
                .HasIndex(pri => new { pri.PurchaseReceiptId, pri.LineNumber })
                .IsUnique();

            // 配置库存与物料的关系
            modelBuilder.Entity<Inventory>()
                .HasOne<Material>(i => i.Material)
                .WithMany()
                .HasForeignKey(i => i.MaterialId);

            // 配置库存与仓库的关系
            modelBuilder.Entity<Inventory>()
                .HasOne<Warehouse>(i => i.Warehouse)
                .WithMany()
                .HasForeignKey(i => i.WarehouseId);

            // 配置库存交易记录与物料的关系
            modelBuilder.Entity<InventoryTransaction>()
                .HasOne<Material>(t => t.Material)
                .WithMany()
                .HasForeignKey(t => t.MaterialId);

            // 配置库存交易记录与仓库的关系
            modelBuilder.Entity<InventoryTransaction>()
                .HasOne<Warehouse>(t => t.Warehouse)
                .WithMany()
                .HasForeignKey(t => t.WarehouseId);

            // 配置库存交易记录与操作人的关系
            modelBuilder.Entity<InventoryTransaction>()
                .HasOne<User>(t => t.Operator)
                .WithMany()
                .HasForeignKey(t => t.OperatorId);

            // 配置库存的唯一性约束(物料ID+仓库ID)
            modelBuilder.Entity<Inventory>()
                .HasIndex(i => new { i.MaterialId, i.WarehouseId })
                .IsUnique();

            // 配置库存交易记录编码唯一
            modelBuilder.Entity<InventoryTransaction>()
                .HasIndex(t => t.TransactionCode)
                .IsUnique();

            // 配置车间编码唯一
            modelBuilder.Entity<Workshop>()
                .HasIndex(w => w.WorkshopCode)
                .IsUnique();

            // 配置车间与部门的关系
            modelBuilder.Entity<Workshop>()
                .HasOne<Department>(w => w.Department)
                .WithMany(d => d.Workshops)
                .HasForeignKey(w => w.DepartmentId);

            // 配置车间与经理的关系
            modelBuilder.Entity<Workshop>()
                .HasOne<User>(w => w.Manager)
                .WithMany()
                .HasForeignKey(w => w.ManagerId);



            // 配置采购入库单与仓库的关系
            modelBuilder.Entity<PurchaseStockIn>()
                .HasOne<Warehouse>(psi => psi.Warehouse)
                .WithMany()
                .HasForeignKey(psi => psi.WarehouseId);

            // 配置采购入库单与供应商的关系
            modelBuilder.Entity<PurchaseStockIn>()
                .HasOne<Supplier>(psi => psi.Supplier)
                .WithMany()
                .HasForeignKey(psi => psi.SupplierId)
                .OnDelete(DeleteBehavior.NoAction);

            // 配置采购入库单明细与物料的关系
            modelBuilder.Entity<PurchaseStockInItem>()
                .HasOne<Material>(psii => psii.Material)
                .WithMany()
                .HasForeignKey(psii => psii.MaterialId);

            // 配置生产入库单与车间的关系
            modelBuilder.Entity<ProductionStockIn>()
                .HasOne<Workshop>(psi => psi.Workshop)
                .WithMany()
                .HasForeignKey(psi => psi.WorkshopId)
                .OnDelete(DeleteBehavior.NoAction);

            // 配置生产入库单与仓库的关系
            modelBuilder.Entity<ProductionStockIn>()
                .HasOne<Warehouse>(psi => psi.Warehouse)
                .WithMany()
                .HasForeignKey(psi => psi.WarehouseId);

            // 配置生产入库单明细与生产入库单的关系
            modelBuilder.Entity<ProductionStockInItem>()
                .HasOne<ProductionStockIn>(psii => psii.ProductionStockIn)
                .WithMany(psi => psi.ProductionStockInItems)
                .HasForeignKey(psii => psii.ProductionStockInId);

            // 配置生产入库单明细与物料的关系
            modelBuilder.Entity<ProductionStockInItem>()
                .HasOne<Material>(psii => psii.Material)
                .WithMany()
                .HasForeignKey(psii => psii.MaterialId);

            // 配置其他入库单与仓库的关系
            modelBuilder.Entity<OtherStockIn>()
                .HasOne<Warehouse>(osi => osi.Warehouse)
                .WithMany()
                .HasForeignKey(osi => osi.WarehouseId);

            // 配置其他入库单编码唯一
            modelBuilder.Entity<OtherStockIn>()
                .HasIndex(osi => osi.StockInCode)
                .IsUnique();

            // 配置其他入库单明细与其他入库单的关系
            modelBuilder.Entity<OtherStockInItem>()
                .HasOne<OtherStockIn>(osii => osii.OtherStockIn)
                .WithMany(osi => osi.OtherStockInItems)
                .HasForeignKey(osii => osii.OtherStockInId);

            // 配置其他入库单明细与物料的关系
            modelBuilder.Entity<OtherStockInItem>()
                .HasOne<Material>(osii => osii.Material)
                .WithMany()
                .HasForeignKey(osii => osii.MaterialId);

            // 配置其他入库单明细的唯一性约束(入库单ID+行号)
            modelBuilder.Entity<OtherStockInItem>()
                .HasIndex(osii => new { osii.OtherStockInId, osii.LineNumber })
                .IsUnique();
        }
    }
}